﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using NPOI.HSSF.UserModel;

namespace JJY.CommonHelper
{
    public class ExcelHelper
    {
        public DataTable GetDataTableFromExcel(string filePath)
        {
            DataTable result = new DataTable();
            try
            {
                string conn = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                string sql = "select * from [Sheet1$]";
                OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn));
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                result = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw(ex);
            }
            return result;
        }

        public DataTable GetDataTableByNPOI(string filePath)
        {

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                DataTable result = new DataTable();

                //string[] columnSet = Settings.EmployeeInfoExcelColumn.ToString().Split(',');
                //foreach (string column in columnSet)
                //{
                //    result.Columns.Add(column);
                //}

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                while (rows.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow dr = result.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        HSSFCell cell = row.GetCell(i) as HSSFCell;

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    result.Rows.Add(dr);
                }

                return result;
            }
        }
    }
}
